package com.zp.dao.impl;

import com.zp.dao.UserDao;
import com.zp.domain.User;
import com.zp.domain.vo.UserVo;
import com.zp.utils.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 此类使用了Druid和 DBUtil
 */
public class UserDaoImpl implements UserDao {


    @Override
    public int insertUser(User user) {
        String sql = "insert into user(sno,username,password,birth,faculty,phone,mail,picture) values(?,?,?,?,?,?,?,?)";
        int row = 0;
        QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
        try {
            row = qr.update(sql,
                    user.getSno(),
                    user.getUsername(),
                    user.getPassword(),
                    user.getBirth(),
                    user.getFaculty(),
                    user.getPhone(),
                    user.getMail(),
                    user.getPicture()
                    );
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    @Override
    public int insertRegister(User user) {
        String sql = "insert into user(sno,username,password,birth,phone,mail) values(?,?,?,?,?,?)";
        int row = 0;
        QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
        try {
            row = qr.update(sql,
                    user.getSno(),
                    user.getUsername(),
                    user.getPassword(),
                    user.getBirth(),
                    user.getPhone(),
                    user.getMail()
                    );
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    @Override
    public int updateUser(User user) {
        String sql = "update user set " +
                "sno = ?, " +
                "username = ?, " +
                "password = ?, " +
                "birth = ?, " +
                "faculty = ?, " +
                "phone = ?, " +
                "mail = ?, " +
                "picture = ? " +
                "where id = ?";
        int row = 0;
        QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
        try {
            row = qr.update(sql,
                    user.getSno(),
                    user.getUsername(),
                    user.getPassword(),
                    user.getBirth(),
                    user.getFaculty(),
                    user.getPhone(),
                    user.getMail(),
                    user.getPicture(),
                    user.getId()
                    );
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    @Override
    public int updateUserBySno(User user) {
        String sql = "update user set " +
                "username = ?, " +
                "password = ?, " +
                "birth = ?, " +
                "faculty = ?, " +
                "phone = ?, " +
                "mail = ?, " +
                "picture = ? " +
                "where sno = ?";
        int row = 0;
        QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
        try {
            row = qr.update(sql,
                    user.getUsername(),
                    user.getPassword(),
                    user.getBirth(),
                    user.getFaculty(),
                    user.getPhone(),
                    user.getMail(),
                    user.getPicture(),
                    user.getSno()
            );
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    @Override
    public long getNumOfUsers() {
        String sql = "select count(1) from user";
        QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
        long numOfUsers = 0;
        try {
            numOfUsers = qr.query(sql, new ScalarHandler<>());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return numOfUsers;
    }

    @Override
    public List<User> listUsers() {
        String sql = "select * from user;";
        QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
        List<User> users = null;
        try {
            users = qr.query(sql, new BeanListHandler<>(User.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return users;
    }

    /**
     * 数据分页
     * @param page 当前页码
     * @param limit 每页的数据量
     * @return
     */
    @Override
    public List<UserVo> listUsersVo(int page, int limit) {
        String sql = "select sno, username, birth, faculty, phone, mail from user limit ?, ?;";
        QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
        List<UserVo> usersVo = null;
        try{
            usersVo = qr.query(sql, new BeanListHandler<>(UserVo.class), (page - 1) * limit, limit);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return usersVo;
    }

    @Override
    public User getUser(int id) {
        String sql = "select * from user where id = ?;";
        User user = null;
        QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
        try {
            user = qr.query(sql, new BeanHandler<>(User.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return user;
    }

    @Override
    public User getUserBySno(String sno) {
        String sql = "select * from user where sno = ?;";
        User user = null;
        QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
        try {
            user = qr.query(sql, new BeanHandler<>(User.class), sno);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return user;
    }

    @Override
    public UserVo getUserVoBySno(String sno) {
        String sql = "select * from user where sno = ?";
        UserVo user = null;
        QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
        try {
            user = qr.query(sql, new BeanHandler<>(UserVo.class), sno);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return user;
    }

    @Override
    public int deleteUser(Integer id) {
        String sql = "delete from user where id = ?";
        int row = 0;
        QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
        try {
            row = qr.update(sql, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    @Override
    public int deleteUsersBySnos(Integer[] snos) {
        int row = 0;
        for (Integer sno:snos) {
            int i = deleteUserBySno(sno);
            row += i;
        }
        return row;
    }

    @Override
    public int deleteUserBySno(Integer sno) {
        String sql = "delete from user where sno = ?";
        int row = 0;
        QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
        try {
            row = qr.update(sql, sno);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }
}
